﻿using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace VIT.Provider
{
    public class Media : Media_Base
    {
        #region Variable
        string user = HttpContext.Current.Session["userName"].ToString();
        #endregion

        #region Constructor
        public Media()
        {
            this.Id = -1;
            this.AlbumId = 0;
            this.LanguageId = string.Empty;
            this.Title = string.Empty;
            this.Filename = string.Empty;
            this.Description = string.Empty;            
            this.Tag = string.Empty;
            this.Type = string.Empty;
            this.Orders = 0;
            this.Published = true;
        }
        #endregion

        #region Lists
        public DataTable Filter(string SortColumn, string albumId)
        {
            string strQuery; if (SortColumn == "") SortColumn = "Orders";
            strQuery = "select Id, AlbumId, Title, Filename, Description, Orders, Tag, Views, Published from Media a left join Media_Language l on Id=MediaId and LanguageID=@LanguageID where AlbumId=@AlbumId and Deleted=0 order by " + SortColumn;

            SqlParameter LanguageId = new SqlParameter("@LanguageID", this.LanguageId);
            SqlParameter Album = new SqlParameter("@AlbumId", albumId);
            return ExecuteDataset(false, strQuery, LanguageId, Album).Tables[0];
        }   

        public bool Delete(int delete, int id)
        {
            string strQuery = "update Media set Deleted=@Deleted ,ModifyDate=@Now, ModifyByUser=@User, DeleteByUser=@DeleteByUser where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter D = new SqlParameter("@Deleted", delete);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter DeleteByUser = new SqlParameter("@DeleteByUser", (delete == 1) ? user : "");
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            if (ExecuteNonQuery(false, strQuery, Id, D, User, Now, DeleteByUser) > 0)
            {
                strQuery = "update Media_Language set Published=@Published where MediaId=@ID";
                SqlParameter ID = new SqlParameter("@ID", id);
                SqlParameter P = new SqlParameter("@Published", (delete == 0) ? 1 : 0);
                return ExecuteNonQuery(false, strQuery, ID, P) > 0;
            }

            return false;
        }

        public bool Publish(int publish, int id)
        {
            string strQuery = "update Media_Language set Published=@Published, ModifyDate=@Now, ModifyByUser=@User where MediaId=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter P = new SqlParameter("@Published", publish);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, strQuery, Id, P, Now, User) > 0;
        }

        public bool Order(string order, int id)
        {
            string strQuery = "update Media_Language set Orders=Orders " + order + " 1, ModifyDate=@Now, ModifyByUser=@User where MediaId=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, strQuery, Id, Now, User) > 0;
        }
        #endregion

        #region Edit
        public void GetSingle()
        {
            if (Exist())
            {
                string strQuery = "select AlbumId, Title, Filename, Description, Tag, Type, Orders, Published from Media a left join Media_Language l on Id=MediaId and LanguageID=@LanguageId where Id=@Id";
                SqlParameter Id = new SqlParameter("@Id", this.Id);
                SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
                DataTable dt = ExecuteDataset(false, strQuery, Id, LanguageId).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[0]["Orders"].ToString()))
                        this.Orders = int.Parse(dt.Rows[0]["Orders"].ToString());
                    this.Title = dt.Rows[0]["Title"].ToString();
                    this.Filename = dt.Rows[0]["Filename"].ToString();
                    this.Description = dt.Rows[0]["Description"].ToString();
                    this.Tag = dt.Rows[0]["Tag"].ToString();
                    this.Type = dt.Rows[0]["Type"].ToString();
                    this.AlbumId = Convert.ToInt32(dt.Rows[0]["AlbumId"]);
                    if(!string.IsNullOrEmpty(dt.Rows[0]["Published"].ToString()))
                        this.Published = (bool)dt.Rows[0]["Published"];     
                }
            }
            else this.Id = -1;
        }

        public bool hasType(string type)
        {
            string strQuery = "select MediaId from Media_Language where Type like '%" + type + "%' and LanguageId=@LanguageId and MediaId=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            return ExecuteScalar(false, strQuery, Id, LanguageId) != null;
        }

        public bool Exist()
        {
            string strQuery = "select Id from Media where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            return ExecuteScalar(false, strQuery, Id) != null;
        }

        public bool Exist(int id, string languageid)
        {
            string strQuery = "select MediaId from Media_Language where MediaId=@Id and LanguageID=@LanguageId";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", languageid);
            return ExecuteScalar(false, strQuery, Id, LanguageId) != null;
        }

        public bool Save()
        {
            if (Exist())
            {
                Delete(0, this.Id);
                UpdateObj(true);

                if (Exist(this.Id, this.LanguageId)) UpdateObjLang(true);
                else InsertObjLang(true);
            }
            else
            {
                this.Id = InsertObj(true);
                if (this.Id > 0) InsertObjLang(true);
            }
            return false;
        }

        public int InsertObj(bool isTransaction)
        {
            string sqlCat = "insert into Media(AlbumId, Filename, Deleted, CreateDate, ModifyDate, CreateByUser, ModifyByUser) values (@AlbumId, @Filename, 0, @Now, @Now, @User, @User)";

            SqlParameter AlbumId = new SqlParameter("@AlbumId", this.AlbumId);
            SqlParameter Filename = new SqlParameter("@Filename", this.Filename);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, sqlCat, AlbumId, Now, User);
        }

        public int InsertObjLang(bool isTransaction)
        {
            string sqlLang = "insert into Media_Language (MediaId, LanguageId, Title, Description, Tag, Type, Orders, Published, CreateDate, ModifyDate, CreateByUser, ModifyByUser) values (@MediaId, @LanguageId, @Title, @Description, @Tag, @Type, @Orders, @Published,  @Now, @Now, @User, @User)";
            SqlParameter MediaId = new SqlParameter("@MediaId", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Title = new SqlParameter("@Title", this.Title);
            SqlParameter Description = new SqlParameter("@Description", this.Description);
            SqlParameter Tag = new SqlParameter("@Tag", this.Tag);
            SqlParameter Type = new SqlParameter("@Type", this.Type);
            SqlParameter Orders = new SqlParameter("@Orders", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);
            return ExecuteNonQuery(isTransaction, sqlLang, MediaId, LanguageId, Title, Description, Tag, Type, Orders, Published, Now, User);
        }

        public bool UpdateObj(bool isTransaction)
        {
            string sqlCat = "update Media set AlbumId=@AlbumId, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            SqlParameter AlbumId = new SqlParameter("@AlbumId", this.AlbumId);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, sqlCat, Id, AlbumId, Now, User) > 0;
        }

        public int UpdateObjLang(bool isTransaction)
        {
            string sqlLang = "update Media_Language set Title=@Title, Description=@Description, Tag=@Tag, Type=@Type, Orders=@Orders, Published=@Published, ModifyDate=@Now, ModifyByUser=@User where MediaId=@MediaId and LanguageId=@LanguageId";
            SqlParameter MediaId = new SqlParameter("@MediaId", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Title = new SqlParameter("@Title", this.Title);
            SqlParameter Description = new SqlParameter("@Description", this.Description);
            SqlParameter Tag = new SqlParameter("@Tag", this.Tag);
            SqlParameter Type = new SqlParameter("@Type", this.Type);
            SqlParameter Orders = new SqlParameter("@Orders", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(false, sqlLang, MediaId, LanguageId, Title, Description, Tag, Type, Orders, Published, Now, User);
        }
        #endregion

        #region Trash
        public bool AlbumIdExist(int delete, string id)
        {
            string strQuery = "select c.Id from Media a, Album c where a.Id=@Id and c.Deleted=@Delete and a.AlbumId=c.Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter d = new SqlParameter("@Delete", delete);
            object obj = ExecuteScalar(false, strQuery, Id, d);
            return (obj != null) ? true : false;
        }

        public DataTable GetRecycleBin(string SortColumn)
        {
            string strQuery;
            if(SortColumn=="")
                strQuery = "select Id, Image, Filename, AlbumId, CreateDate, ModifyDate, CreateByUser, ModifyByUser, DeleteByUser from Media  where Deleted=1";
            else
                strQuery = "select Id, Image, Filename, AlbumId, CreateDate, ModifyDate, CreateByUser, ModifyByUser, DeleteByUser from Media where Deleted=1 Order By " + SortColumn;
            return ExecuteDataset(false, strQuery).Tables[0];
        }

        public bool Cleared(string id)
        {
            string strQuery = "delete from Media where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            return ExecuteNonQuery(false, strQuery, Id) > 0;
        }

        public string GetFileName()
        {
            string strQuery = "select Filename from Media where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            object obj = ExecuteScalar(false, strQuery, Id);
            if (obj != null) return obj.ToString();
            return "";
        }
        #endregion
    }
}
